CREATE        PROCEDURE [dbo].[asi_ImportDeferredIncomeConversionWork]
@financialEntityKey uniqueidentifier,
@organizationKey uniqueidentifier
AS
DECLARE @finEntityCode nvarchar(50)
DECLARE @orgKey uniqueidentifier
DECLARE @targetAcctCode nvarchar(50)
DECLARE @invKey uniqueidentifier
DECLARE @invDate datetime
DECLARE @amountConverted decimal(18,4)
DECLARE @transKey uniqueidentifier
DECLARE @transDate datetime
DECLARE @glAccountKey uniqueidentifier
DECLARE @deferredGLAccountKey uniqueidentifier
DECLARE @invLineKey uniqueidentifier
DECLARE @productKey uniqueidentifier
DECLARE @deferredAcctCode nvarchar(50)
DECLARE @glTypeDistribution int
DECLARE @glTypeDeferredIncome int
DECLARE @journalTypeDefIncConversion int
DECLARE @firstMonthFiscalYear int
DECLARE @fiscalPeriod int

SET @glTypeDeferredIncome = 6
SET @glTypeDistribution = 1
SET @journalTypeDefIncConversion = 6
/*     This stored procedure will do the following:
    1. Set up a cursor on the DeferralConversionWork table.
    2. For each row:
        - Create a GL transaction for the converted deferred income.
*/
SELECT @finEntityCode = FinancialEntityCode, @firstMonthFiscalYear = FirstMonthFiscalYear FROM FinancialEntity WHERE FinancialEntityKey = @financialEntityKey
SET @transDate = GETDATE()
SET @fiscalPeriod = dbo.CalculateFiscalPeriod(@transDate, @firstMonthFiscalYear)
DECLARE crsDeferralWork CURSOR FOR
SELECT DeferredConversionWork.TargetGLAccountCode, DeferredConversionWork.OrganizationKey, DeferredConversionWork.InvoiceKey,
DeferredConversionWork.AmountConverted, DeferredConversionWork.InvoiceLineKey, DeferredConversionWork.DeferredGLAccountCode,
InvoiceLine.ProductKey
FROM DeferredConversionWork
LEFT OUTER JOIN InvoiceLine on DeferredConversionWork.InvoiceLineKey = InvoiceLine.InvoiceLineKey
WHERE FinancialEntityCode = @finEntityCode AND OrganizationKey = @organizationKey
OPEN crsDeferralWork
FETCH NEXT FROM crsDeferralWork into @targetAcctCode, @orgKey, @invKey, @amountConverted, @invLineKey, @deferredAcctCode, @productKey
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @transKey = NEWID()
    INSERT GLTransactionMain
    (GLTransactionKey, TransactionDate, InvoiceKey, FinancialEntityKey, JournalEntryTypeCode, FiscalPeriod, CreatedOn)
    VALUES (@transKey, @transDate, @invKey, @financialEntityKey, @journalTypeDefIncConversion, @fiscalPeriod, getdate())
    SELECT @glAccountKey = GLAccountKey FROM GLAccount WHERE GLAccountCode = @targetAcctCode
    SELECT @deferredGLAccountKey = GLAccountKey FROM GLAccount WHERE GLAccountCode = @deferredAcctCode
    INSERT GLTransactionLine
    (GLTransactionLineKey, GLTransactionKey, Amount, GLAccountKey, Description,
    InvoiceLineKey, GLEntryType, TransactionSequenceNumber, ProductKey)
    VALUES (NEWID(), @transKey, -1 * @amountConverted, @glAccountKey, 'DeferredIncome Conversion', @invLineKey, 'Distribution', 0, @productKey)
    INSERT GLTransactionLine
    (GLTransactionLineKey, GLTransactionKey, Amount, GLAccountKey, Description,
    InvoiceLineKey, GLEntryType, TransactionSequenceNumber, ProductKey)
    VALUES (NEWID(), @transKey, @amountConverted, @deferredGLAccountKey, 'DeferredIncome Conversion', @invLineKey, 'DeferredIncome', 0, @productKey)
    FETCH NEXT FROM crsDeferralWork into @targetAcctCode, @orgKey, @invKey, @amountConverted, @invLineKey, @deferredAcctCode, @productKey
END
CLOSE crsDeferralWork
DEALLOCATE crsDeferralWork

GO